package com.munichosica.myapp.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.apache.log4j.Logger;

import com.munichosica.myapp.dao.PersonaDao;
import com.munichosica.myapp.dto.Persona;
import com.munichosica.myapp.dto.PersonaPk;
import com.munichosica.myapp.exceptions.PersonaDaoException;
import com.munichosica.myapp.exceptions.SpGetCliente1DaoException;

public class PersonaDaoImpl extends AbstractDAO implements PersonaDao
{
	/** 
	 * The factory class for this DAO has two versions of the create() method - one that
takes no arguments and one that takes a Connection argument. If the Connection version
is chosen then the connection will be stored in this attribute and will be used by all
calls to this DAO, otherwise a new Connection will be allocated for each operation.
	 */
	protected java.sql.Connection userConn;

	protected static final Logger logger = Logger.getLogger( PersonaDaoImpl.class );

	/** 
	 * All finder methods in this class use this SELECT constant to build their queries
	 */
	protected final String SQL_SELECT = "SELECT PERCODIGO_I, PERNOMBRE_V, PERPATERNO_V, PERMATERNO_V, PEREDAD_I, PERDNI_V FROM " + getTableName() + "";

	/** 
	 * Finder methods will pass this value to the JDBC setMaxRows method
	 */
	protected int maxRows;

	/** 
	 * SQL INSERT statement for this table
	 */
	protected final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( PERNOMBRE_V, PERPATERNO_V, PERMATERNO_V, PEREDAD_I, PERDNI_V ) VALUES ( ?, ?, ?, ?, ? );SELECT @@IDENTITY";

	/** 
	 * SQL UPDATE statement for this table
	 */
	protected final String SQL_UPDATE = "UPDATE " + getTableName() + " SET PERNOMBRE_V = ?, PERPATERNO_V = ?, PERMATERNO_V = ?, PEREDAD_I = ?, PERDNI_V = ? WHERE PERCODIGO_I = ?";

	/** 
	 * SQL DELETE statement for this table
	 */
	protected final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE PERCODIGO_I = ?";

	/** 
	 * Index of column PERCODIGO_I
	 */
	protected static final int COLUMN_PERCODIGO_I = 1;

	/** 
	 * Index of column PERNOMBRE_V
	 */
	protected static final int COLUMN_PERNOMBRE_V = 2;

	/** 
	 * Index of column PERPATERNO_V
	 */
	protected static final int COLUMN_PERPATERNO_V = 3;

	/** 
	 * Index of column PERMATERNO_V
	 */
	protected static final int COLUMN_PERMATERNO_V = 4;

	/** 
	 * Index of column PEREDAD_I
	 */
	protected static final int COLUMN_PEREDAD_I = 5;

	/** 
	 * Index of column PERDNI_V
	 */
	protected static final int COLUMN_PERDNI_V = 6;

	/** 
	 * Number of columns
	 */
	protected static final int NUMBER_OF_COLUMNS = 6;

	/** 
	 * Index of primary-key column PERCODIGO_I
	 */
	protected static final int PK_COLUMN_PERCODIGO_I = 1;

	/** 
	 * Inserts a new row in the PERSONA table.
	 */
	public PersonaPk insert(Persona dto) throws PersonaDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			stmt = conn.prepareCall( SQL_INSERT );
			// no bind statement for auto increment column 'PERCODIGO_I
			stmt.setString( COLUMN_PERNOMBRE_V - 1, dto.getPernombreV() );
			stmt.setString( COLUMN_PERPATERNO_V - 1, dto.getPerpaternoV() );
			stmt.setString( COLUMN_PERMATERNO_V - 1, dto.getPermaternoV() );
			if (dto.isPeredadINull()) {
				stmt.setNull( COLUMN_PEREDAD_I - 1, java.sql.Types.INTEGER );
			} else {
				stmt.setInt( COLUMN_PEREDAD_I - 1, dto.getPeredadI() );
			}
		
			stmt.setString( COLUMN_PERDNI_V - 1, dto.getPerdniV() );
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_INSERT + " with DTO: " + dto);
			}
		
			stmt.execute();
			int rows = stmt.getUpdateCount();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected");
			}
		
		
			// retrieve values from IDENTITY columns
			boolean moreResults = true;
			while (moreResults || rows != -1) {
				try {
					rs = stmt.getResultSet();
				}
				catch (IllegalArgumentException e) {
					logger.error( e);
				}
		
				if (rs != null) {
					rs.next();
					dto.setPercodigoI( rs.getInt( 1 ) );
				}
		
				moreResults = stmt.getMoreResults();
				rows = stmt.getUpdateCount();
			}
		
			return dto.createPk();
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new PersonaDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Updates a single row in the PERSONA table.
	 */
	public void update(PersonaPk pk, Persona dto) throws PersonaDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_UPDATE + " with DTO: " + dto);
			}
		
			stmt = conn.prepareStatement( SQL_UPDATE );
			int index=1;
			stmt.setString( index++, dto.getPernombreV() );
			stmt.setString( index++, dto.getPerpaternoV() );
			stmt.setString( index++, dto.getPermaternoV() );
			if (dto.isPeredadINull()) {
				stmt.setNull( index++, java.sql.Types.INTEGER );
			} else {
				stmt.setInt( index++, dto.getPeredadI() );
			}
		
			stmt.setString( index++, dto.getPerdniV() );
			stmt.setInt( 6, pk.getPercodigoI() );
			int rows = stmt.executeUpdate();
			reset(dto);
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}
		
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new PersonaDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Deletes a single row in the PERSONA table.
	 */
	public void delete(PersonaPk pk) throws PersonaDaoException
	{
		long t1 = System.currentTimeMillis();
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL_DELETE + " with PK: " + pk);
			}
		
			stmt = conn.prepareStatement( SQL_DELETE );
			stmt.setInt( 1, pk.getPercodigoI() );
			int rows = stmt.executeUpdate();
			long t2 = System.currentTimeMillis();
			if (logger.isDebugEnabled()) {
				logger.debug( rows + " rows affected (" + (t2-t1) + " ms)");
			}
		
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new PersonaDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Returns the rows from the PERSONA table that matches the specified primary-key value.
	 */
	public Persona findByPrimaryKey(PersonaPk pk) throws PersonaDaoException
	{
		return findByPrimaryKey( pk.getPercodigoI() );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PERCODIGO_I = :percodigoI'.
	 */
	public Persona findByPrimaryKey(int percodigoI) throws PersonaDaoException
	{
		Persona ret[] = findByDynamicSelect( SQL_SELECT + " WHERE PERCODIGO_I = ?", new Object[] {  new Integer(percodigoI) } );
		return ret.length==0 ? null : ret[0];
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria ''.
	 */
	public Persona[] findAll() throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " ORDER BY PERCODIGO_I", null );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PERCODIGO_I = :percodigoI'.
	 */
	public Persona[] findWherePercodigoIEquals(int percodigoI) throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE PERCODIGO_I = ? ORDER BY PERCODIGO_I", new Object[] {  new Integer(percodigoI) } );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PERNOMBRE_V = :pernombreV'.
	 */
	public Persona[] findWherePernombreVEquals(String pernombreV) throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE PERNOMBRE_V = ? ORDER BY PERNOMBRE_V", new Object[] { pernombreV } );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PERPATERNO_V = :perpaternoV'.
	 */
	public Persona[] findWherePerpaternoVEquals(String perpaternoV) throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE PERPATERNO_V = ? ORDER BY PERPATERNO_V", new Object[] { perpaternoV } );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PERMATERNO_V = :permaternoV'.
	 */
	public Persona[] findWherePermaternoVEquals(String permaternoV) throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE PERMATERNO_V = ? ORDER BY PERMATERNO_V", new Object[] { permaternoV } );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PEREDAD_I = :peredadI'.
	 */
	public Persona[] findWherePeredadIEquals(int peredadI) throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE PEREDAD_I = ? ORDER BY PEREDAD_I", new Object[] {  new Integer(peredadI) } );
	}

	/** 
	 * Returns all rows from the PERSONA table that match the criteria 'PERDNI_V = :perdniV'.
	 */
	public Persona[] findWherePerdniVEquals(String perdniV) throws PersonaDaoException
	{
		return findByDynamicSelect( SQL_SELECT + " WHERE PERDNI_V = ? ORDER BY PERDNI_V", new Object[] { perdniV } );
	}

	/**
	 * Method 'PersonaDaoImpl'
	 * 
	 */
	public PersonaDaoImpl()
	{
	}

	/**
	 * Method 'PersonaDaoImpl'
	 * 
	 * @param userConn
	 */
	public PersonaDaoImpl(final java.sql.Connection userConn)
	{
		this.userConn = userConn;
	}

	/** 
	 * Sets the value of maxRows
	 */
	public void setMaxRows(int maxRows)
	{
		this.maxRows = maxRows;
	}

	/** 
	 * Gets the value of maxRows
	 */
	public int getMaxRows()
	{
		return maxRows;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "Capacitacion..PERSONA";
	}

	/** 
	 * Fetches a single row from the result set
	 */
	protected Persona fetchSingleResult(ResultSet rs) throws SQLException
	{
		if (rs.next()) {
			Persona dto = new Persona();
			populateDto( dto, rs);
			return dto;
		} else {
			return null;
		}
		
	}

	/** 
	 * Fetches multiple rows from the result set
	 */
	protected Persona[] fetchMultiResults(ResultSet rs) throws SQLException
	{
		Collection resultList = new ArrayList();
		while (rs.next()) {
			Persona dto = new Persona();
			populateDto( dto, rs);
			resultList.add( dto );
		}
		
		Persona ret[] = new Persona[ resultList.size() ];
		resultList.toArray( ret );
		return ret;
	}

	/** 
	 * Populates a DTO with data from a ResultSet
	 */
	protected void populateDto(Persona dto, ResultSet rs) throws SQLException
	{
		dto.setPercodigoI( rs.getInt( COLUMN_PERCODIGO_I ) );
		dto.setPernombreV( rs.getString( COLUMN_PERNOMBRE_V ) );
		dto.setPerpaternoV( rs.getString( COLUMN_PERPATERNO_V ) );
		dto.setPermaternoV( rs.getString( COLUMN_PERMATERNO_V ) );
		dto.setPeredadI( rs.getInt( COLUMN_PEREDAD_I ) );
		if (rs.wasNull()) {
			dto.setPeredadINull( true );
		}
		
		dto.setPerdniV( rs.getString( COLUMN_PERDNI_V ) );
	}

	/** 
	 * Resets the modified attributes in the DTO
	 */
	protected void reset(Persona dto)
	{
	}

	/** 
	 * Returns all rows from the PERSONA table that match the specified arbitrary SQL statement
	 */
	public Persona[] findByDynamicSelect(String sql, Object[] sqlParams) throws PersonaDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			// construct the SQL statement
			final String SQL = sql;
		
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}
		
			// prepare statement
			stmt = conn.prepareStatement( SQL );
			stmt.setMaxRows( maxRows );
		
			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}
		
		
			rs = stmt.executeQuery();
		
			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new PersonaDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	/** 
	 * Returns all rows from the PERSONA table that match the specified arbitrary SQL statement
	 */
	public Persona[] findByDynamicWhere(String sql, Object[] sqlParams) throws PersonaDaoException
	{
		// declare variables
		final boolean isConnSupplied = (userConn != null);
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			// get the user-specified connection or get a connection from the ResourceManager
			conn = isConnSupplied ? userConn : ResourceManager.getConnection();
		
			// construct the SQL statement
			final String SQL = SQL_SELECT + " WHERE " + sql;
		
		
			if (logger.isDebugEnabled()) {
				logger.debug( "Executing " + SQL);
			}
		
			// prepare statement
			stmt = conn.prepareStatement( SQL );
			stmt.setMaxRows( maxRows );
		
			// bind parameters
			for (int i=0; sqlParams!=null && i<sqlParams.length; i++ ) {
				stmt.setObject( i+1, sqlParams[i] );
			}
		
		
			rs = stmt.executeQuery();
		
			// fetch the results
			return fetchMultiResults(rs);
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new PersonaDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			if (!isConnSupplied) {
				ResourceManager.close(conn);
			}
		
		}
		
	}

	@Override
	public List<Persona> findByCriterio(String criterio, String texto)
			throws PersonaDaoException {

		final boolean _isConnSupplied = (userConn != null);
		Connection _conn = null;
		CallableStatement _stmt = null;
		ResultSet rs = null;
		
		List<Persona> list=new ArrayList<Persona>();
		try {
			_conn = _isConnSupplied ? userConn : ResourceManager.getConnection();
			_stmt = _conn.prepareCall( "{call Capacitacion..SP_GET_PERSONABYCRITERIO;1(?, ?)}" );
			_stmt.setString(1, criterio);
			_stmt.setString(2, texto);
			
			boolean results=_stmt.execute();
            if(results){
                rs=_stmt.getResultSet();
                Persona persona=null;
                while(rs.next()){
                    persona=new Persona();
                    persona.setPercodigoI(rs.getInt("CODIGO"));
                    persona.setPernombreV(rs.getString("NOMBRE"));
                    persona.setPerpaternoV(rs.getString("PATERNO"));
                    persona.setPermaternoV(rs.getString("MATERNO"));
                    persona.setPeredadI(rs.getInt("EDAD"));
                    persona.setPerdniV(rs.getString("DNI"));
                    list.add(persona);
                }
            }
		}
		catch (Exception _e) {
			logger.error( "Exception: " + _e.getMessage(), _e );
			throw new PersonaDaoException( "Exception: " + _e.getMessage(), _e );
		}
		finally {
			ResourceManager.close(rs);
			ResourceManager.close(_stmt);
			if (!_isConnSupplied) {
				ResourceManager.close(_conn);
			}
		
		}
		return list;
	}

}
